package multiplex.dataaccess.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import multiplex.dataaccess.DaoException;
import multiplex.model.Filme;
import multiplex.model.Sala;
import multiplex.model.Sessao;

public class SessaoDao extends AbstractDao{
	
	public Sessao get(int id) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement("SELECT * FROM Sessao WHERE id =?");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			if(rs.next()){
				Sessao sessao = new Sessao();
				fillSessao(sessao, rs);
				st.close();
				return sessao;
			}
		} catch(Exception e){
			throw new DaoException(e);
		}
		return null;
	}

	public List<Sessao> getAll() throws DaoException {
		List<Sessao> sessaoList = new ArrayList<Sessao>();
		try{
			PreparedStatement st = getConnection().prepareStatement("SELECT * FROM Sessao");
			
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Sessao sessao = new Sessao();
				fillSessao(sessao, rs);
				sessaoList.add(sessao);
			}
			st.close();
			return sessaoList;
		}catch(Exception e){
			throw new DaoException(e);
		}
	}
	
	public List<Sessao> getAllFromSala(Sala sala) throws DaoException {
		
		List<Sessao> sessaoList = new ArrayList<Sessao>();
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"SELECT * FROM Sessao WHERE Sessao.Sala_id=?");
			st.setInt(1, sala.getId());
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Sessao sessao = new Sessao();
				fillSessao(sessao, rs);
				sessaoList.add(sessao);
			}
			st.close();
			return sessaoList;
		}catch(Exception e){
			throw new DaoException(e);
		}
	}
	
	public List<Sessao> getAllFromSalaAndFilme(Sala sala, Filme filme) 
		throws DaoException {

		List<Sessao> sessaoList = new ArrayList<Sessao>();
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"SELECT Sessao.id AS id, Sessao.dataInicio AS dataInicio, " +
					"Sessao.Filme_id AS Filme_id, Sessao.Sala_id AS Sala_id FROM " +
					"Sessao, Filme WHERE " +
					"Sessao.Filme_id= Filme.id AND " +
					"Sessao.Sala_id=? AND Sessao.Filme_id=?");
			st.setInt(1, sala.getId());
			st.setInt(2, filme.getId());
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				Sessao sessao = new Sessao();
				fillSessao(sessao, rs);
				sessaoList.add(sessao);
			}
			st.close();
			return sessaoList;
		}catch(Exception e){
			throw new DaoException(e);
		}
	}

	public void save(Sessao sessao) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"INSERT INTO Sessao (dataInicio, Filme_id, Sala_id) VALUES (?,?,?)", 
					Statement.RETURN_GENERATED_KEYS);
			fillPreparatedStatement(st, sessao);
			st.execute();
			ResultSet rs = st.getGeneratedKeys();
			rs.next();
			sessao.setId(rs.getInt(1));
			st.close();
		}catch(Throwable e){
			throw new DaoException(e);
		}
	}
	
	public void update(Sessao sessao) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"UPDATE Sessao SET dataInicio=?, Filme_id=?, Sala_id=? WHERE id=?");
			fillPreparatedStatement(st, sessao);
			st.setInt(5, sessao.getId());
			st.execute();
			st.close();
		}catch(Throwable e){
			throw new DaoException(e);
		}
	}

	public void remove(Sessao sessao) throws DaoException {
		try{
			PreparedStatement st = getConnection().prepareStatement(
					"DELETE FROM Sessao WHERE id=?");
			st.setInt(1, sessao.getId());
			st.execute();
			st.close();
		} catch(Throwable e){
			throw new DaoException(e);
		}
	}
	
	private void fillSessao(Sessao sessao, ResultSet rs) throws SQLException {
		sessao.setId(rs.getInt("id"));
		Calendar temp = Calendar.getInstance();
		temp.setTime(rs.getDate("dataInicio"));
		sessao.setDataInicio(temp);
		int filmeId = rs.getInt("Filme_id");
		FilmeDao filmeDao = new FilmeDao();
		sessao.setFilme( filmeDao.get(filmeId) );
		int salaId = rs.getInt("Sala_id");
		SalaDao salaDao = new SalaDao();
		sessao.setSala( salaDao.get(salaId) );
	}
	
	private void fillPreparatedStatement(PreparedStatement st, Sessao sessao) throws SQLException {
		st.setDate(1, new Date(sessao.getDataInicio().getTimeInMillis()));
		st.setInt(2, sessao.getFilme().getId());
		st.setInt(3, sessao.getSala().getId());
	}
	
}
